# -*- coding: utf-8 -*-
"""
Created on Fri May 29 09:24:31 2020

@author: Long
"""

#pyinstaller --onefile --nowindowed Excel固定区域截图.py
import os,time
import win32api
import win32con
from win32com.client import Dispatch
from PIL import ImageGrab
from qiniu import Auth, put_file
import requests
import json

'''
启用win32模块导出excel的图表，图表需要打开加载缓存才能导出
'''
 
    
class Pyxlchart(object):
    """
    This class exports charts in an Excel Spreadsheet to the FileSystem
    win32com libraries are required.
    """
    def __init__(self):
        '''
        初始化图表
        '''     
        self.sheetname1 ='Sheet1'  #excel文件的sheet页名称	
        self.area_list1 = ['A1:D22'] #截图区域
        self.picture_list1 =['E:/数据/红冠日报/图片/pic1'] #生成截图的文件名
        self.sheetname2 ='欠货数据'  #excel文件的sheet页名称	
        self.area_list2 = ['A1:I4'] #截图区域
        self.picture_list2 =['E:/数据/红冠日报/图片/pic2']
        self.sheetname3 ='当月回款数据'  #excel文件的sheet页名称	
        self.area_list3 = ['A1:I4'] #截图区域
        self.picture_list3 =['E:/数据/红冠日报/图片/pic3']
        self.sheetname4 ='客户应收情况'  #excel文件的sheet页名称	
        self.area_list4 = ['A1:U4'] #截图区域
        self.picture_list4 =['E:/数据/红冠日报/图片/pic4']
        self.sheetname5 ='对账函未回'  #excel文件的sheet页名称	
        self.area_list5 = ['A1:M10'] #截图区域
        self.picture_list5 =['E:/数据/红冠日报/图片/pic5']
        self.sheetname6 ='对账差异未处理'  #excel文件的sheet页名称	
        self.area_list6 = ['A1:J9'] #截图区域
        self.picture_list6 =['E:/数据/红冠日报/图片/pic6']
        
    def __del__(self):
        pass
    def start_export(self,_visible=True):
        if self.WorkbookDirectory == '':
            return "WorkbookDirectory not set"
        else:
            print ("start_export")
            self._export(_visible)
    def _export(self,_visible):
        """
        Exports Charts as determined by the settings in class variabels.
        """
        excel = Dispatch("excel.application")
        #启用独立的进程调用excel，Dispatch容易冲突【会强行关闭正在打开的excel】
        #使用 DispatchEx为单独调用线程，不影响已经打开的excel
        #excel.Visible = True
        excel.Visible = _visible
        wb = excel.Workbooks.Open(os.path.join(self.WorkbookDirectory ,self.WorkbookFilename))
        time.sleep(5)  # 等5秒等待进程打开加载文档
        def yidong(x):
            
            for k in range(x):
                print ("sheet")
                time.sleep(0.5)
                win32api.keybd_event(17, 0, 0, 0)  # 键盘按下  ctrl键
                win32api.keybd_event(34, 0, 0, 0)  # ctrl+pageDown的组合会跳转sheet，20次跳转可以到最后的图表
            win32api.keybd_event(36, 0, 0, 0)  # 键盘按下  home键，和上个按键形成组合键，回到第一行开头
            win32api.keybd_event(17, 0, win32con.KEYEVENTF_KEYUP, 0)
            win32api.keybd_event(36, 0, win32con.KEYEVENTF_KEYUP, 0)

            # 当表格过大时，只能保存到页面显示的图标，故此需要先循环翻页将所有图片加载
            for i in range(3):  # 翻页加载所有图表
                print ("down")
                win32api.keybd_event(34, 0, 0, 0)  # 每次读取之后翻页
                win32api.keybd_event(34, 0, win32con.KEYEVENTF_KEYUP, 0)
                time.sleep(0.5)
        #上传图片
        def get_img_url(key,local_file):
            access_key = "DZnCErimkn2yQrn4aYel3JX7vPXKRonlvDFoVh1e"
            secret_key = "FBEHIFyMG28nWZrn316df-ny5bmIz_LanRWtabCi"
            q = Auth(access_key, secret_key)
            bucket_name = "qiniu730173201"#网上的空间
            token = q.upload_token(bucket_name, key)#删掉旧图片
            ret, info = put_file(token, key, local_file)#上传新图片
            base_url = "http://zzsy.zeus.cn/"#二级域名
            url = base_url + '/' + key
            private_url = q.private_download_url(url)#图片新网址
            #r = requests.get(private_url)
            #assert r.status_code == 200` </pre>
            return private_url
        
        def dingmessage(str1,img_url0,ddurl):#上传dingding
            webhook = ddurl#机器人url
            header = {
                "Content-Type": "application/json",
                "Charset": "UTF-8"
                }
            message ={
                "msgtype": "markdown",
                "markdown": {"title": "报表",
                             "text":"# "+str1+"\n\n"+
                             "#### 报表\n\n" +
                             "> ![screenshot]("+img_url0+")\n" +
                             "> ###### 由小红罐发布\n"
                             },
                "at": {"isAtAll": 0}
                }#报表内容
            message_json = json.dumps(message)#json格式化
            info = requests.post(url=webhook,data=message_json,headers=header)#机器人发送信息
            print(info.text)            

        yidong(1)
        self._excel_catch_screen(wb,excel,self.sheetname1,self.area_list1,self.picture_list1)
        
        
        yidong(3)
        self._excel_catch_screen(wb,excel,self.sheetname2,self.area_list2,self.picture_list2)
        yidong(1)
        self._excel_catch_screen(wb,excel,self.sheetname3,self.area_list3,self.picture_list3)
        yidong(1)
        self._excel_catch_screen(wb,excel,self.sheetname4,self.area_list4,self.picture_list4)
        yidong(1)
        self._excel_catch_screen(wb,excel,self.sheetname5,self.area_list5,self.picture_list5)
        yidong(1)
        self._excel_catch_screen(wb,excel,self.sheetname6,self.area_list6,self.picture_list6)
        
        
        
        #ws = wb.Sheets(sheetname)
#        Range("D2").Select
#        ws.Selection.AutoFilter
#        ws.ActiveSheet.Range("$A$2:$M$10").AutoFilter Field:=4, Criteria1:="河北红冠"
#        ws.ActiveSheet.Range("$A$2:$M$10").AutoFilter Field:=4, Criteria1:="红冠大客户"
#        ws.ActiveSheet.Range("$A$2:$M$10").AutoFilter Field:=4, Criteria1:="江苏红冠"
#        ws.ActiveSheet.Range("$A$2:$M$10").AutoFilter Field:=4, Criteria1:="江西红冠"
#        ws.Selection.AutoFilter
        
        # 使用打开excel的方式，则模拟键盘事件触发加载所有图表
        
        #wb.Close(SaveChanges=0)
        #excel.Quit()
#        for i in range(1,7):
#            key = 'data/%s'%("pic"+str(i)+".png")#网络路径
#            img_url = get_img_url(key,"E:/数据/红冠日报/图片/pic"+str(i)+".PNG")#上传下载
#            time.sleep(1)
#            #钉钉发送
#            ddurl="https://oapi.dingtalk.com/robot/send?access_token=3dca7e5f6705dcc6bb4b80a2fcc0c8675e92c5d6687cbb1ded275ce6f3f20609"
#            dingmessage("报表",img_url,ddurl)
        
#        key = 'data/%s'%("红冠事业部日报推送模板(1).xlsx")#网络路径
#        img_url = get_img_url(key,"E:/数据/红冠日报/红冠事业部日报推送模板(1).xlsx")#上传下载
#        time.sleep(1)
#        #钉钉发送
#        ddurl="https://oapi.dingtalk.com/robot/send?access_token=3dca7e5f6705dcc6bb4b80a2fcc0c8675e92c5d6687cbb1ded275ce6f3f20609"
#        dingmessage("报表",img_url,ddurl)

        
    def _excel_catch_screen(self,wb,excel,sheetname, screen_area,picture_name):
        ws = wb.Sheets(sheetname)  #选择sheet
		#循环处理每个截图区域
        for i in range (0,len(screen_area)):
            ws.Range(screen_area[i]).CopyPicture()  #复制图片区域
            time.sleep(0.5)
            print ("复制")
            ws.Paste()  #粘贴  
            time.sleep(0.5)
            print ("粘贴")
            name = picture_name[i]
            excel.Selection.ShapeRange.Name = name  #将刚刚选择的Shape重命名，避免与已有图片混淆
            time.sleep(0.5)
            print (excel.Selection.ShapeRange.Name)
            #ws.Shapes(excel.Selection.ShapeRange.Name).Copy()  # 选择图片
            ws.Shapes(picture_name[i]).Copy()  # 选择图片
            time.sleep(0.5)
            print ("选择")
            img = ImageGrab.grabclipboard()  # 获取剪贴板的图片数据
            time.sleep(0.5)
            print ("获取")
            img_name = picture_name[i]+ ".PNG" #生成图片的文件名
            img.save(img_name)  #保存图片
            
            #记录已处理的截图区域
            x=screen_area[i]
            #记录已处理的截图名称
            y=picture_name[i]
            #打印日志
            print('区域：' + x + ' 图片：' +y)
            print('剩余计数：'+ str(len(screen_area))+' ' +str(len(picture_name)))

            
docPath = r'E:\数据\红冠日报'
if __name__=='__main__':
    Ect = Pyxlchart()
    Ect.WorkbookDirectory = docPath
    Ect.WorkbookFilename = '红冠事业部日报推送模板(1).xlsx'
    Ect.SheetName = ""   #图表所在的sheet名称
    Ect.ExportPath = r'E:\数据\红冠日报\图片'  #图片的导出路径
    Ect.start_export()
    
